package com.cjbdi.service;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.cjbdi.util.NumUtil;

/**
 * EqTyService二期新增指数统一处理接口（独任、长期超审限、二审服判、调节结案、人民陪审）
 */
@Service
@Transactional
public class BaseService{
	@Autowired
    Environment env;
	@Autowired  
    @PersistenceContext  
    private EntityManager entityManager;// 注入EntityManager实例 
	@Autowired 
	TFypzService tFypzService; 
	
	public void getGyDataIntoMap(Map dataMap, List qgTableList,List qgSelfList,List qgTableList3, String fymc,String prefix) {//高院报告第二段
		StringBuffer rankQG3 = new StringBuffer();//全国前三
		List listQG3 = new ArrayList();
		Map rowSelf = new HashMap();
		double gyzs = 0;
		double qgjz = 0;	
		//dataMap.put("qhfy", "--");
		if(fymc.equals("全国")) {
			for(int i=0;i<qgTableList.size();i++) {//全国排名列表（最大值、最小值）
				Map row = (Map)qgTableList.get(i);
				String fy = NumUtil.toSheng(row.get("fy").toString())+"法院";
				row.put("fy", fy);
				row.put("id", i+1);
				int r = Integer.parseInt(row.get("rank").toString());
				if (i < 3) {
					listQG3.add(row);
				}
				double zs = NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2);
				double zsLastYear = NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2);
				double cz = NumUtil.toNxiaoshu(row.get("cz").toString(), 2);
				double hbzs = row.get("hbzs")!=null?NumUtil.toNxiaoshu(row.get("hbzs").toString(), 2):0;
				double hbcz = row.get("hbcz")!=null?NumUtil.toNxiaoshu(row.get("hbcz").toString(), 2):0;
				row.put("hbzs", hbzs);
				row.put("hbcz", hbcz);
				row.put("lastyearzs", zsLastYear==999999?"--":zsLastYear);
				row.put("yearzs", zs==999999?"--":zs);
				row.put("cz", zsLastYear==999999||zs==999999?"--":cz);
			}
		}else {
			for (int i = 0; i < qgTableList.size(); i++) {// 全国排名列表（最大值、最小值）
				Map row = (Map) qgTableList.get(i);
				double zs = NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2);
				double zsLastYear = NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2);
				double cz = NumUtil.toNxiaoshu(row.get("cz").toString(), 2);
				String fy = NumUtil.toSheng(row.get("fy").toString()) + "法院";
				row.put("lastyearzs", zsLastYear);
				row.put("yearzs", zs);
				row.put("cz", cz);
				row.put("fy", fy);
				double hbzs = row.get("hbzs")!=null?NumUtil.toNxiaoshu(row.get("hbzs").toString(), 2):0;
				double hbcz = row.get("hbcz")!=null?NumUtil.toNxiaoshu(row.get("hbcz").toString(), 2):0;
				row.put("hbzs", hbzs);
				row.put("hbcz", hbcz);
				int r = Integer.parseInt(row.get("rank").toString());
				if (i == 0) {
					if (prefix.equals("sfgl_rjajh")) {
						String rankQG1 = NumUtil.toSheng(row.get("fy").toString());
						if (NumUtil.toSheng(fymc).equals(rankQG1)) {
							dataMap.put("qhfy", "全国");
						} else {
							dataMap.put("qhfy", rankQG1);
						}
					}
					row.put("id", "1");
					if(prefix.contains("shzl")) {
						row.put("fy", "全国最小值地区");
					}else if (prefix.contains("fajh") || prefix.contains("gzl") || prefix.contains("gjlj")
							|| prefix.contains("csx")) {
							row.put("fy", "全国最小值法院");
					}else {
						row.put("fy", "全国最大值法院");
					}
					qgTableList3.add(row);
				}
				if (fy.equals(NumUtil.toSheng(fymc) + "法院")) {
					rowSelf.put("id", "2");
					rowSelf.put("lastyearzs", zsLastYear);
					rowSelf.put("yearzs", zs);
					rowSelf.put("cz", cz);
					if(prefix.contains("shzl")) {
						rowSelf.put("fy", NumUtil.toSheng(fy));
					}else {
						rowSelf.put("fy", fy);
					}
					rowSelf.put("hbzs", hbzs);
					rowSelf.put("hbcz", hbcz);
					qgTableList3.add(rowSelf);
					dataMap.put(prefix + "_rank_qg", "第"+NumUtil.toChineseNum(r)+"位");
					gyzs = zs;
					if (prefix.equals("sfgl_csx")) {
						dataMap.put("sfgl_csxzs", zs);
						if (cz > 0) {
							dataMap.put("sfgl_csx_tb", "上升" + Math.abs(cz));// 同比
						} else if (cz < 0) {
							dataMap.put("sfgl_csx_tb", "下降" + Math.abs(cz));// 同比
						} else {
							dataMap.put("sfgl_csx_tb", "不变");// 同比
						}
					}
				}
				if (i == qgTableList.size() - 1) {
					row.put("id", "3");
					if(prefix.contains("shzl")) {
						row.put("fy", "全国最大值地区");
					}else if (prefix.contains("fajh") || prefix.contains("gzl") || prefix.contains("gjlj")
							|| prefix.contains("csx")) {
						row.put("fy", "全国最大值法院");
					}else {
						row.put("fy", "全国最小值法院");
					}
					qgTableList3.add(row);
				}
			}
		}
		if(qgSelfList.size()>0) {//全国均值
			Map row = (Map)qgSelfList.get(0);
			row.put("id", "4");
			row.put("fy", "全国平均值");
			row.put("lastyearzs", NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2));
			row.put("yearzs", NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2));
			row.put("cz", NumUtil.toNxiaoshu(row.get("cz").toString(), 2));
			double hbzs = row.get("hbzs")!=null?NumUtil.toNxiaoshu(row.get("hbzs").toString(), 2):0;
			double hbcz = row.get("hbcz")!=null?NumUtil.toNxiaoshu(row.get("hbcz").toString(), 2):0;
			row.put("hbzs", hbzs);
			row.put("hbcz", hbcz);
			qgjz = NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2);
			qgTableList3.add(row);
			if(prefix.equals("sfgl_csx")) {
				if(fymc.equals("全国")) {
					dataMap.put("sfgl_csxzs", NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2));
					double czqg =  NumUtil.toNxiaoshu(row.get("cz").toString(), 2);
					if(czqg>0) {
	        			dataMap.put("sfgl_csx_tb", "上升"+Math.abs(czqg));//同比
	        		}else if(czqg<0) {
	        			dataMap.put("sfgl_csx_tb", "下降"+Math.abs(czqg));//同比
	        		}else {
	        			dataMap.put("sfgl_csx_tb", "不变");//同比
	        		}
				}
			}
		}
		
		if(listQG3.size()>3) {//只取前三个
			listQG3 = listQG3.subList(0, 3);
		}
		for(int i=0;i<listQG3.size();i++) {
			Map row = (Map)listQG3.get(i);
			rankQG3.append(row.get("fy").toString()+"（"+NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2)+"）");		
			rankQG3.append("、");
		}
		dataMap.put(prefix + "_rank3_qg", rankQG3.length()>1?rankQG3.substring(0, rankQG3.length()-1):"--");//全国排名前三省份
		if(prefix.contains("gzl")) {
			dataMap.put(prefix + "_jz_qg", Math.abs(qgjz));
			double jzcz = NumUtil.toNxiaoshu(Math.abs(gyzs) - Math.abs(qgjz), 2);
			if(jzcz>0) {//和均值做比较
				dataMap.put(prefix + "_jz_cz", "绝对值高"+Math.abs(jzcz));
				dataMap.put(prefix + "_fxjb", "高");//风险级别
			}else if(jzcz<0) {
				dataMap.put(prefix + "_jz_cz", "绝对值低"+Math.abs(jzcz));
				dataMap.put(prefix + "_fxjb", "低");
			}else {
				dataMap.put(prefix + "_jz_cz", "绝对值相等");
				dataMap.put(prefix + "_fxjb", "高");
			}
		}else {
			dataMap.put(prefix + "_jz_qg", qgjz);
			double jzcz = NumUtil.toNxiaoshu(gyzs - qgjz, 2);
			if(jzcz>0) {//和均值做比较
				dataMap.put(prefix+"_jz_cz", "高"+Math.abs(jzcz));
				dataMap.put(prefix + "_fxjb", "高");
			}else if(jzcz<0) {
				dataMap.put(prefix+"_jz_cz", "低"+Math.abs(jzcz));
				dataMap.put(prefix + "_fxjb", "低");
			}else {
				dataMap.put(prefix+"_jz_cz", "相等");
				dataMap.put(prefix + "_fxjb", "高");
			}
		}
	}
	
	public void getZyDetailDataIntoMap(Map dataMap, List zjTableList, List gylist, String fymc,String prefix) {//高院报告第三段
		String rankZJ1 = "";
		StringBuffer rankZJ3 = new StringBuffer();//全省前三
		StringBuffer rankZJH3 = new StringBuffer();// 全省后三
		for(int i=0;i<zjTableList.size();i++) {
			Map row = (Map)zjTableList.get(i);
			row.put("id", i+2);
			row.put("lastyearzs", NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2));
			row.put("yearzs", NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2));
			row.put("cz", NumUtil.toNxiaoshu(row.get("cz").toString(), 2));
			double hbzs = row.get("hbzs")!=null?NumUtil.toNxiaoshu(row.get("hbzs").toString(), 2):0;
			double hbcz = row.get("hbcz")!=null?NumUtil.toNxiaoshu(row.get("hbcz").toString(), 2):0;
			row.put("hbzs", hbzs);
			row.put("hbcz", hbcz);
			if(i==0) {
				rankZJ1 = row.get("fy").toString();
			}
			if(i<3) {
				rankZJ3.append(row.get("fy").toString()+"（"+NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2)+"）");
				if(i<2)
					rankZJ3.append("、");
			}
		}
		if(zjTableList.size()>3) {
			for(int i=1;i<4;i++) {
				Map row = (Map) zjTableList.get(zjTableList.size()-i);
				rankZJH3.append(row.get("fy").toString() + "（" + NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2) + "）");
				rankZJH3.append("、");
			}
		}
		dataMap.put(prefix + "_rank1_zj", rankZJ1);//中级排名第一
		dataMap.put(prefix + "_rank3_zj", rankZJ3);//中级排名前三
		dataMap.put(prefix + "_rankh3_zj", rankZJH3.length()>1?rankZJH3.substring(0, rankZJH3.length()-1):"--");//中级排名后三
		if(!(prefix.equals("gzzs_wdzs")||prefix.equals("spxg_ysfp"))) {
			Map gyself = new HashMap();//当前高院本身的指数值
			gyself.put("id", 1);
			gyself.put("fy", NumUtil.toSheng(fymc)+"高院");
			gyself.put("lastyearzs",0);
			gyself.put("yearzs",0);
			gyself.put("cz",0);
			gyself.put("hbzs",0);
			gyself.put("hbcz",0);
			if(gylist.size()>0) {
				Map row = (Map)gylist.get(0);
				gyself.put("lastyearzs", NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2));
				gyself.put("yearzs", NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2));
				gyself.put("cz", NumUtil.toNxiaoshu(row.get("cz").toString(), 2));
				double hbzs = row.get("hbzs")!=null?NumUtil.toNxiaoshu(row.get("hbzs").toString(), 2):0;
				double hbcz = row.get("hbcz")!=null?NumUtil.toNxiaoshu(row.get("hbcz").toString(), 2):0;
				gyself.put("hbzs", hbzs);
				gyself.put("hbcz", hbcz);
			}
			zjTableList.add(0,gyself);
		}else {
			for(int i=0;i<zjTableList.size();i++) {
				Map row = (Map)zjTableList.get(i);
				row.put("id", i+1);
			}
		}
	}
	
	public List getXsList(String kind, String type, String date, String fybm){ //判断收案量是否小于2000
    	Query query = entityManager.createNativeQuery("SELECT\r\n" + 
    			"	xs \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_result_ajztqk \r\n" + 
    			"WHERE\r\n" + 
    			"	type = '"+type+"' \r\n" + 
    			"	AND date='"+date+"'\r\n" + 
    			"	AND kind = '"+kind+"' \r\n" + 
    			"	AND jbfy= '"+fybm+"'");
    	query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
	}
	
	//同等收案量排名表格
    public List getTdSalTableList(String tableName, String type, String date, String fybm,boolean desc, String casetype){  
    	StringBuffer sql = new StringBuffer("SELECT\r\n" + 
    	    			"	sal,jbfy,\r\n" + 
    	    			"	REPLACE ( fyjc, '法院', '' ) || '法院' AS fy,\r\n" + 
    	    			"	result\r\n" + 
    	    			"FROM\r\n" + tableName+"," + 
    	    			"	sfzs_czrk \r\n" + 
    	    			"WHERE\r\n" + 
    	    			tableName+".jbfy = sfzs_czrk.fybm \r\n");
    			    	if(!tableName.equals("sfzs_result_csx")) {
    						sql.append(" and type='"+type+"'");
    					}
    	    			if(!casetype.equals("")) {
    	    	    		sql.append(" and casetype='"+casetype+"'");
    	    	    	}
    	    			sql.append("	AND sfzs_czrk.YEAR = '"+NumUtil.getPastDate(date,"yyyy")+"' \r\n" + 
    	    			"	AND "+tableName+".date = '"+date+"'\r\n" + 
    	    			"	AND kind = '中' \r\n" + 
    	    			"	AND sal IN ( SELECT sal FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+NumUtil.getPastDate(date,"yyyy")+"' AND fybm = '"+fybm+"' ) \r\n");
    	    	if(desc) {
    	    		sql.append("ORDER BY result desc");
    	    	}else {
    	    		sql.append("ORDER BY result");
    	    	}
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }

	public static void getTdSalDataIntoMap(Map dataMap, List tdSalTableList, String fymc,String prefix) {
		dataMap.put(prefix+"_rank1_tdsal", "--");
		dataMap.put(prefix+"_size_tdsal", "--");
		dataMap.put(prefix+"_jz_tdsal", "--");
		dataMap.put(prefix+"_jz_tdsal_cz", "--");
		dataMap.put(prefix+"_count_tdsal", "--");
		dataMap.put(prefix+"_rankh_tdsal", "--");
		dataMap.put(prefix+"_rank_tdsal", "第--位");
		// 同等收案量排行
		if(fymc.contains("北京")||fymc.contains("天津")||fymc.contains("上海")||fymc.contains("重庆")) {
			return;
		}
		double sum = 0;
		double zyzs = 0;
		for (int i = 0; i < tdSalTableList.size(); i++) {
			Map row = (Map) tdSalTableList.get(i);
			if(prefix.equals("sfgl_csx")) {
				sum = sum + Math.abs(NumUtil.toNxiaoshu(row.get("result").toString(), 2));
			}else {
				sum = sum + NumUtil.toNxiaoshu(row.get("result").toString(), 2);
			}
			if (i == 0) {
				dataMap.put(prefix+"_rank1_tdsal", NumUtil.toNxiaoshu(row.get("result").toString(), 2));
				dataMap.put(prefix+"_size_tdsal", row.get("sal").toString());
			}
			if (row.get("fy").equals(fymc)) {
				dataMap.put(prefix+"_rank_tdsal", "第"+NumUtil.toChineseNum(i+1)+"位");
				zyzs = NumUtil.toNxiaoshu(row.get("result").toString(), 2);
			}
			if (i == tdSalTableList.size() - 1) {
				dataMap.put(prefix+"_rankh_tdsal", NumUtil.toNxiaoshu(row.get("result").toString(), 2));
			}
			row.put("id", i + 1);
		}
		double jz = NumUtil.toNxiaoshu(tdSalTableList.size()==0?0:sum / tdSalTableList.size(), 2);
		dataMap.put(prefix+"_jz_tdsal", jz);
    	double jzcz = NumUtil.toNxiaoshu(zyzs - jz, 2);
    	if(jzcz>0) {//和均值做比较
			dataMap.put(prefix+"_jz_tdsal_cz", "高"+Math.abs(jzcz));
		}else if(jzcz<0) {
			dataMap.put(prefix+"_jz_tdsal_cz", "低"+Math.abs(jzcz));
		}else {
			dataMap.put(prefix+"_jz_tdsal_cz", "相等");
		}
		
		
		dataMap.put(prefix+"_count_tdsal", tdSalTableList.size());
	}
	 //同等规模城市排名表格
    public List getTdZjTableList(String tableName, String type, String date, String fybm, boolean desc, String casetype){  
    	StringBuffer sql = new StringBuffer();
    	if(type.equals("year")) {
    		sql = new StringBuffer("SELECT jbfy,replace(fyjc,'法院','')||'法院' AS fy,\r\n" + 
        			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )as lastyearzs,\r\n" + 
        			"sum(case when date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
        			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )) as cz from "+tableName+",sfzs_czrk where "+tableName+".jbfy=sfzs_czrk.fybm");
        	if(!tableName.equals("sfzs_result_csx")) {
    			sql.append(" and type='"+type+"'");
    		}
        	if(!casetype.equals("")) {
        		sql.append(" and casetype='"+casetype+"'");
        	}
        	sql.append(" and sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and (kind = '中' or (kind='高' and fybm in('100','200','900','M00'))) and size in (select size from sfzs_czrk where sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and fybm = '"+fybm+"') GROUP BY jbfy,fyjc");
        	if(desc) {
        		sql.append(" ORDER BY yearzs desc");
        	}else {
        		sql.append(" ORDER BY yearzs");
        	}
    	}else {
    		sql = new StringBuffer("select jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-hbzs) as hbcz,(yearzs-lastyearzs) as cz from(SELECT jbfy,replace(fyjc,'法院','')||'法院' AS fy,\r\n" + 
        			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' and type='"+type+"' then result else 0 end )as lastyearzs,\r\n" + 
        			"sum(case when date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then result else 0 end) as hbzs,\r\n" + 
        			"sum(case when date='"+date+"' and type='"+type+"' then result else 0 end) as yearzs\r\n" +
        			" from "+tableName+",sfzs_czrk where "+tableName+".jbfy=sfzs_czrk.fybm");
        	if(!casetype.equals("")) {
        		sql.append(" and casetype='"+casetype+"'");
        	}
        	sql.append(" and sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and (kind = '中' or (kind='高' and fybm in('100','200','900','M00'))) and size in (select size from sfzs_czrk where sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and fybm = '"+fybm+"') GROUP BY jbfy,fyjc");
        	if(desc) {
        		sql.append(" ORDER BY yearzs desc");
        	}else {
        		sql.append(" ORDER BY yearzs");
        	}
        	sql.append(" )t");
    	}
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //区县法院基本信息
    public List getQxInfo(String tableName, String type, String date, String fybm,boolean desc,String casetype){  
    	StringBuffer sql = new StringBuffer("select (select count(*) from sfzs_czrk where sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and size in (select size from sfzs_czrk where sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and fybm = '"+fybm+"')) as c,(select fyjc from sfzs_czrk where sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and fybm = '"+fybm.substring(0, 1)+"'||'00') as xq,num,jbfy,fy,rank,size from(select num,jbfy,fy,result,size,rank() over(order by result");
    	if(desc) {
    		sql.append(" desc");
    	}
    	sql.append(") as rank from(select size,num_czh as num,jbfy,fyjc as fy,result from "+tableName+",sfzs_czrk where "+tableName+".jbfy=sfzs_czrk.fybm");
    	if(!tableName.equals("sfzs_result_csx")) {
    		sql.append(" and type='"+type+"'");
    	}
    	if(!casetype.equals("")) {
    		sql.append(" and casetype='"+casetype+"'");
    	}
    	sql.append(" and sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and kind ='中' and date='"+date+"' and size in (select size from sfzs_czrk where sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and fybm = '"+fybm+"') ) t )t1 where jbfy='"+fybm+"'");
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //区县法院在所属高院辖区内排名
    public List getRankInGyxq(String tableName,String type, String date, String fybm,boolean desc,String casetype){  
    	StringBuffer sql = new StringBuffer("SELECT\r\n" + 
    			"	rank \r\n" + 
    			"FROM\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	jbfy,\r\n" + 
    			"	fy,\r\n" + 
    			"	lastyearzs,\r\n" + 
    			"	yearzs,\r\n" + 
    			"	cz,\r\n" + 
    			"	rank ( ) over ( ORDER BY yearzs");
    	if(desc) {
    		sql.append(" DESC \r\n");
    	}
    	sql.append(") AS rank \r\n" + 
    			"FROM\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	jbfy,\r\n" + 
    			"	REPLACE ( fymc, '人民法院', '法院' ) AS fy,\r\n" + 
    			"	SUM( CASE WHEN DATE = '"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' THEN RESULT ELSE 0 END ) AS lastyearzs,\r\n" + 
    			"	SUM( CASE WHEN DATE = '"+date+"' THEN RESULT ELSE 0 END ) AS yearzs,\r\n" + 
    			"	(\r\n" + 
    			"	SUM( CASE WHEN DATE = '"+date+"' THEN RESULT ELSE 0 END ) - SUM( CASE WHEN DATE = '"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' THEN RESULT ELSE 0 END ) \r\n" + 
    			"	) AS cz \r\n" + 
    			"FROM " + tableName +
    			" WHERE\r\n" + 
    			"	kind = '辖' \r\n");
    	if(!tableName.equals("sfzs_result_csx")) {
			sql.append(" and type='"+type+"'");
		} 
    	sql.append("	AND jbfy LIKE '"+fybm.substring(0, 1)+"%' \r\n");
    	if(!casetype.equals("")) {
    		sql.append("	AND casetype = '"+ casetype +"'" );
    	}
    	sql.append("	AND RIGHT ( jbfy, 1 ) <> '0' \r\n" + 
    			"	AND RIGHT ( fymc, 5 ) <> '（已撤销）' \r\n" + 
    			"GROUP BY\r\n" + 
    			"	jbfy,\r\n" + 
    			"	fymc \r\n" + 
    			"ORDER BY\r\n" + 
    			"	yearzs ");
	    	if(desc) {
	    		sql.append("DESC \r\n");
	    	}
	    	sql.append(	"	) t \r\n" + 
    			"	) t1 \r\n" + 
    			"WHERE\r\n" + 
    			"	jbfy = '"+fybm+"'");
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        if(rows.size()==0) {
        	rows = Arrays.asList("0");
        }
        return rows;
    }
    public static void getTdCityDataIntoMap(Map dataMap, List infoList, List tdTableList, String prefix, String fybm) {
    	List tdTableList3 = new ArrayList();
    	Map rowSelf = new HashMap();
    	double pnum = 0;//人口数
		String citySize = "";//城市规模
		String ssxq = "";//所属辖区
		String rankTd = "--";//在同等规模城市中排名
		int c = 0;//同等规模城市的数量
		if(infoList.size()>0) {
			Map row = (Map)infoList.get(0);
			if(prefix.equals("gzzs_gkzs")) {
				pnum = NumUtil.toNxiaoshu(row.get("num").toString(), 2);
			}
			citySize = row.get("size").toString();
			ssxq = row.get("xq").toString();
			rankTd = row.get("rank")==null?"--":"第" + NumUtil.toChineseNum(row.get("rank").toString()) + "位";
			c = Integer.parseInt(row.get("c").toString());
		}
		if(prefix.equals("gzzs_gkzs")) {
			dataMap.put(prefix+"_rk", pnum+"万");
		}
		dataMap.put(prefix+"_citysize", citySize);
		dataMap.put(prefix+"_ssxq", ssxq);
		dataMap.put(prefix+"_rank_td", "第" + rankTd + "位");
		dataMap.put(prefix+"_td_count", c);
		
		double sumyear = 0;
    	double sumlastyear = 0;
    	double sumhb = 0;
		double zyzs = 0;
		double zs = 0;
		double zsLastYear = 0;
		double cz = 0;
		String fy = "";
		dataMap.put(prefix+"_rank1_td", "--");
		dataMap.put(prefix+"_rankh_td", "--");
		dataMap.put(prefix+"_jz_td", "--");
		dataMap.put(prefix+"_jz_td_cz", "--");
		for(int i=0;i<tdTableList.size();i++) {
			Map row = (Map)tdTableList.get(i);
			zs = NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2);
			zsLastYear = NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2);
			cz = NumUtil.toNxiaoshu(row.get("cz").toString(), 2);
			fy = row.get("fy").toString();
			row.put("lastyearzs", zsLastYear);
			row.put("yearzs", zs);
			row.put("cz", cz);
			double hbzs = row.get("hbzs")!=null?NumUtil.toNxiaoshu(row.get("hbzs").toString(), 2):0;
			double hbcz = row.get("hbcz")!=null?NumUtil.toNxiaoshu(row.get("hbcz").toString(), 2):0;
			row.put("hbzs", hbzs);
			row.put("hbcz", hbcz);
			
			if(prefix.contains("gzl")) {
				sumyear = sumyear + Math.abs(zs);
				sumlastyear = sumlastyear + Math.abs(zsLastYear);
				sumhb = sumhb + Math.abs(hbzs);
			}else {
				sumyear = sumyear + zs;
				sumlastyear = sumlastyear + zsLastYear;
				sumhb = sumhb + hbzs;
			}
			
			if(i==0) {		
				dataMap.put(prefix+"_rank1_td", zs);
				row.put("id", "1");
				if(prefix.contains("fajh")||prefix.contains("gzl")||prefix.contains("gjlj")||prefix.contains("csx")||prefix.contains("shzl")) {
					row.put("fy", "同等城市最小值");
				}else {
					row.put("fy", "同等城市最大值");
				}
				tdTableList3.add(row);
			}
			if(row.get("jbfy").toString().equals(fybm)) {
				zyzs = zs;
				rowSelf.put("id", "2");
            	rowSelf.put("lastyearzs", zsLastYear);
            	rowSelf.put("yearzs", zs);
            	rowSelf.put("cz", cz);
            	rowSelf.put("fy", fy);
            	rowSelf.put("hbzs", hbzs);
            	rowSelf.put("hbcz", hbcz);
				tdTableList3.add(rowSelf);
			}
			if(i==tdTableList.size()-1) {
				dataMap.put(prefix+"_rankh_td", zs);
				row.put("id", "3");
				if(prefix.contains("fajh")||prefix.contains("gzl")||prefix.contains("gjlj")||prefix.contains("csx")||prefix.contains("shzl")) {
					row.put("fy", "同等城市最大值");
				}else {
					row.put("fy", "同等城市最小值");
				}
				tdTableList3.add(row);
			}
		}
		double jzyear = NumUtil.toNxiaoshu(tdTableList.size()==0?0:sumyear/tdTableList.size(), 2);
    	double jzlastyear = NumUtil.toNxiaoshu(tdTableList.size()==0?0:sumlastyear / tdTableList.size(), 2);
    	double jzhb = NumUtil.toNxiaoshu(tdTableList.size()==0?0:sumhb / tdTableList.size(), 2);
		dataMap.put(prefix+"_jz_td", jzyear);
		double jzcz = NumUtil.toNxiaoshu(zyzs - jzyear, 2);
		if(jzcz>0) {//和均值做比较
			dataMap.put(prefix+"_jz_td_cz", "高"+Math.abs(jzcz));
		}else if(jzcz<0) {
			dataMap.put(prefix+"_jz_td_cz", "低"+Math.abs(jzcz));
		}else {
			dataMap.put(prefix+"_jz_td_cz", "相等");
		}
		Map row = new HashMap();
		row.put("id", "4");
		row.put("fy", "同等城市平均值");
		row.put("lastyearzs", jzlastyear);
		row.put("yearzs", jzyear);
		row.put("cz", NumUtil.toNxiaoshu(jzyear-jzlastyear, 2));
		row.put("hbzs", jzhb);
		row.put("hbcz", NumUtil.toNxiaoshu(jzyear-jzhb, 2));
		tdTableList3.add(row);
		tdTableList.clear();
		tdTableList.addAll(tdTableList3);
		
		/*if(tdTableList.size()>20) {
			List sublist = tdTableList.subList(20, tdTableList.size());
			tdTableList.removeAll(sublist);
			Map zyxq = (Map)dataMap.get("zyxq");
			if(rankTd>20) {
				Map dian = new HashMap();
				dian.put("id", "...");
				dian.put("fy", "");
				dian.put("lastyearzs","");
				dian.put("yearzs","");
				dian.put("cz","");
				tdTableList.add(dian);
				zyxq.put("id", rankTd);
				tdTableList.add(zyxq);	
			}
		}*/
    }
    public static void getTdZxDataIntoMap(Map dataMap, List tdTableList, String fybm,String prefix) {
    	double zs = 0;
		double zsLastYear = 0;
		double cz = 0;
		double sumyear = 0;
    	double sumlastyear = 0;
    	double sumhb = 0;
		double zyzs = 0;
		String fy = "";
		List tdTableList3 = new ArrayList();
		Map rowSelf = new HashMap();
		dataMap.put(prefix+"_rank1_tdzx", "--");
		dataMap.put(prefix+"_jz_tdzx", "--");
		dataMap.put(prefix+"_td_count", "--");
		dataMap.put(prefix+"_rankh_tdzx", "--");
		dataMap.put(prefix+"_jz_tdzx", "--");
		dataMap.put(prefix+"_jz_tdzx_cz", "--");
		Iterator it = tdTableList.iterator();
		while(it.hasNext()) {
			Map row = (Map)it.next();
    		zs = NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2);
			zsLastYear = NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2);
			cz = NumUtil.toNxiaoshu(row.get("cz").toString(), 2);
			if(zs==999999) {
				it.remove();
			}
		}
		for(int i=0;i<tdTableList.size();i++) {
			Map row = (Map)tdTableList.get(i);
			zs = NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2);
			zsLastYear = NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2);
			cz = NumUtil.toNxiaoshu(row.get("cz").toString(), 2);
			row.put("lastyearzs", zsLastYear);
			row.put("yearzs", zs);
			row.put("cz", cz);
			fy = row.get("fy").toString();
			double hbzs = row.get("hbzs")!=null?NumUtil.toNxiaoshu(row.get("hbzs").toString(), 2):0;
			double hbcz = row.get("hbcz")!=null?NumUtil.toNxiaoshu(row.get("hbcz").toString(), 2):0;
			row.put("hbzs", hbzs);
			row.put("hbcz", hbcz);
			if(prefix.contains("gzl")) {
				sumyear = sumyear + Math.abs(zs);
				sumlastyear = sumlastyear + Math.abs(zsLastYear);
				sumhb = sumhb + Math.abs(hbzs);
			}else {
				sumyear = sumyear + zs;
				sumlastyear = sumlastyear + zsLastYear;
				sumhb = sumhb + hbzs;
			}
			if(i==0) {		
				dataMap.put(prefix+"_rank1_tdzx", zs);
				row.put("id", "1");
				if(prefix.contains("fajh")||prefix.contains("gzl")||prefix.contains("gjlj")||prefix.contains("csx")||prefix.contains("shzl")) {
					row.put("fy", "直辖市中院最小值");
				}else {
					row.put("fy", "直辖市中院最大值");
				}
				tdTableList3.add(row);
			}
			if(row.get("jbfy").toString().equals(fybm)) {
				zyzs = zs;
				rowSelf.put("id", "2");
            	rowSelf.put("lastyearzs", zsLastYear);
            	rowSelf.put("yearzs", zs);
            	rowSelf.put("cz", cz);
            	rowSelf.put("fy", fy);
            	rowSelf.put("hbzs", hbzs);
            	rowSelf.put("hbcz", hbcz);
				tdTableList3.add(rowSelf);
            }
			if(i==tdTableList.size()-1) {
				dataMap.put(prefix+"_rankh_tdzx", zs);
				row.put("id", "3");
				if(prefix.contains("fajh")||prefix.contains("gzl")||prefix.contains("gjlj")||prefix.contains("csx")||prefix.contains("shzl")) {
					row.put("fy", "直辖市中院最大值");
				}else {
					row.put("fy", "直辖市中院最小值");
				}
				tdTableList3.add(row);
			}
		}
		double jzyear = NumUtil.toNxiaoshu(tdTableList.size()==0?0:sumyear / tdTableList.size(), 2);
    	double jzlastyear = NumUtil.toNxiaoshu(tdTableList.size()==0?0:sumlastyear / tdTableList.size(), 2);
    	double jzhb = NumUtil.toNxiaoshu(tdTableList.size()==0?0:sumhb / tdTableList.size(), 2);
		dataMap.put(prefix+"_jz_tdzx", jzyear);
		dataMap.put(prefix+"_td_count", tdTableList.size());
		double jzcz = NumUtil.toNxiaoshu(zyzs - jzyear, 2);
		if(jzcz>0) {//和均值做比较
			dataMap.put(prefix+"_jz_tdzx_cz", "高"+Math.abs(jzcz));
		}else if(jzcz<0) {
			dataMap.put(prefix+"_jz_tdzx_cz", "低"+Math.abs(jzcz));
		}else {
			dataMap.put(prefix+"_jz_tdzx_cz", "相等");
		}
		
		Map row = new HashMap();
		row.put("id", "4");
		row.put("fy", "直辖市中院平均值");	
		row.put("lastyearzs", jzlastyear);
		row.put("yearzs", jzyear);
		row.put("cz", NumUtil.toNxiaoshu(jzyear-jzlastyear, 2));
		row.put("hbzs", jzhb);
		row.put("hbcz", NumUtil.toNxiaoshu(jzhb-jzlastyear, 2));
		tdTableList3.add(row);
		tdTableList.clear();
		tdTableList.addAll(tdTableList3);
		
		if(fybm.startsWith("1")) {
			dataMap.put(prefix+"_ssxq", "北京");
		}else if(fybm.startsWith("2")) {
			dataMap.put(prefix+"_ssxq", "天津");
		}else if(fybm.startsWith("9")) {
			dataMap.put(prefix+"_ssxq", "上海");
		}else if(fybm.startsWith("M")) {
			dataMap.put(prefix+"_ssxq", "重庆");
		}
    }
    
  //高、中院辖区指数（均值）
    public List getGyOrZyXqZs(String table, String type, String date, String jbfy, String casetype,String kind) {
    	StringBuffer sql = new StringBuffer();
    	if(type.equals("year")) {
    		sql = new StringBuffer("SELECT jbfy,fymc as fy,\r\n"
							+ "sum(case when date='" + NumUtil.getPastDate(date,"yyyy-MM-dd")
							+ "' then result else 0 end )as lastyearzs,\r\n" + "sum(case when date='" + date
							+ "' then result else 0 end) as yearzs,\r\n" + "(sum(case when date='" + date
							+ "' then result else 0 end) - sum(case when date='" + NumUtil.getPastDate(date,"yyyy-MM-dd")
							+ "' then result else 0 end )) as cz from "+table+" where ");
    		if(kind.equals("高")) {
    			sql.append("jbfy='"+jbfy.substring(0, 1)+"'||'00' ");
    		}else {
    			sql.append("jbfy='"+jbfy.substring(0, 2)+"'||'0' ");
    		}
    		if(!table.equals("sfzs_result_csx")) {
    			sql.append("and type='"+type+"'");
    		}
    		sql.append(" and date in('" + date + "','" + NumUtil.getPastDate(date,"yyyy-MM-dd")
					+ "') and kind ='"+kind+"' ");
    		if(!casetype.equals("")) {
    			sql.append(" and casetype='"+casetype+"'");
    		}
    		sql.append(" GROUP BY jbfy,fymc");
    	}else {
    		sql = new StringBuffer("SELECT jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(yearzs-hbzs) as hbcz from(SELECT jbfy,fymc as fy,\r\n"
					+ "sum(case when date='" + NumUtil.getPastDate(date,"yyyy-MM-dd") + "' and type='"+type+"' then result else 0 end )as lastyearzs,\r\n" 
					+ "sum(case when date='" + NumUtil.getHalfYearReportDate(date, type) + "' and type='"+NumUtil.getHalfYearReportType(type)+"' then result else 0 end) as hbzs,\r\n" 
					+ "sum(case when date='" + date + "' and type='"+type+"' then result else 0 end) as yearzs\r\n" 
					+ "from "+table+" where ");
    		if(kind.equals("高")) {
    			sql.append("jbfy='"+jbfy.substring(0, 1)+"'||'00' ");
    		}else {
    			sql.append("jbfy='"+jbfy.substring(0, 2)+"'||'0' ");
    		}
			sql.append(" and date in('" + date + "','" + NumUtil.getPastDate(date,"yyyy-MM-dd")
					+ "') and kind ='"+kind+"' ");
			if(!casetype.equals("")) {
				sql.append(" and casetype='"+casetype+"'");
			}
			sql.append(" GROUP BY jbfy,fymc)t");
    	}
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    
    
    public void getQxDataIntoMap(Map dataMap, List xqTableList, String fybm,String prefix, List gyList, List zyList) {//基层报告
    	double zyzs = NumUtil.toNxiaoshu(zyList.size()>0?Double.parseDouble(((Map)zyList.get(0)).get("yearzs").toString()):0, 2);
    	double gyzs = NumUtil.toNxiaoshu(gyList.size()>0?Double.parseDouble(((Map)gyList.get(0)).get("yearzs").toString()):0, 2);
    	String ssxq_zy = ((Object[])tFypzService.getParentFymc(fybm,"zhongji").get(0))[0].toString();
    	String ssxq_gy = ((Object[])tFypzService.getParentFymc(fybm,"gaoji").get(0))[0].toString();
    	dataMap.put(prefix+"_ssxq_zy",ssxq_zy);
    	dataMap.put(prefix+"_ssxq_gy",ssxq_gy);
    	dataMap.put(prefix+"_rank_zy",  "--");
    	dataMap.put(prefix+"_rank1",  "--");
    	dataMap.put(prefix+"_rankh",  "--");
    	double zs = 0;
		double zsLastYear = 0;
		double cz = 0;
		double hbzs = 0;
		double hbcz = 0;
		double sumyear = 0;
    	double sumlastyear = 0;
    	String fy = "";
    	//List qxTableList3 = new ArrayList();
    	Map rowSelf = new HashMap();
    	for(int i=0;i<xqTableList.size();i++) {
			Map row = (Map)xqTableList.get(i);
			zs = NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2);
			zsLastYear = NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2);
			cz = NumUtil.toNxiaoshu(row.get("cz").toString(), 2);
			if(row.get("hbzs")!=null&&row.get("hbcz")!=null) {
				hbzs = NumUtil.toNxiaoshu(row.get("hbzs").toString(), 2);
				hbcz = NumUtil.toNxiaoshu(row.get("hbcz").toString(), 2);
				row.put("hbzs", hbzs==999999?"--":hbzs);
				row.put("hbcz", hbzs==999999||zs==999999?"--":hbcz);
			}
			fy = row.get("fy").toString();
			sumyear = sumyear + zs;
			sumlastyear = sumlastyear + zsLastYear;
			row.put("id", i+1);
			/*row.put("lastyearzs", zsLastYear);
			row.put("yearzs", zs);
			row.put("cz", cz);*/
			row.put("lastyearzs", zsLastYear==999999?"--":zsLastYear);
			row.put("yearzs", zs==999999?"--":zs);
			row.put("cz", zsLastYear==999999||zs==999999?"--":cz);
			
			if(i == 0) {
				if(prefix.contains("fajh")||prefix.contains("gzl")||prefix.contains("gjlj")||prefix.contains("csx")||prefix.contains("shzl")) {
					dataMap.put(prefix+"_rankh", zs);
				}else {
					dataMap.put(prefix+"_rank1", zs);
				}
				
			}
			if(fybm.equals(row.get("jbfy").toString())) {
				dataMap.put(prefix+"_rank_zy",  "第" + NumUtil.toChineseNum(i+1) + "位");
				if(prefix.equals("sfgl_csx")) {
					dataMap.put("sfgl_csxzs", zs);
	        		if(cz>0) {
	        			dataMap.put("sfgl_csx_tb", "上升"+Math.abs(cz));//同比
	        		}else if(cz<0) {
	        			dataMap.put("sfgl_csx_tb", "下降"+Math.abs(cz));//同比
	        		}else {
	        			dataMap.put("sfgl_csx_tb", "不变");//同比
	        		}
				}
				double jz_zj_cz = NumUtil.toNxiaoshu(zs-zyzs, 2);
				dataMap.put(prefix + "_jz_zj", zyzs);
				if (jz_zj_cz > 0) {// 和均值做比较
					dataMap.put(prefix + "_jz_zj_cz", "高" + Math.abs(jz_zj_cz));
				} else if (jz_zj_cz < 0) {
					dataMap.put(prefix + "_jz_zj_cz", "低" + Math.abs(jz_zj_cz));
				} else {
					dataMap.put(prefix + "_jz_zj_cz", "相等");
				}
				double jz_gj_cz = NumUtil.toNxiaoshu(zs-gyzs, 2);
				dataMap.put(prefix + "_jz_gj", gyzs);
				if (jz_gj_cz > 0) {// 和均值做比较
					dataMap.put(prefix + "_jz_gj_cz", "高" + Math.abs(jz_gj_cz));
				} else if (jz_gj_cz < 0) {
					dataMap.put(prefix + "_jz_gj_cz", "低" + Math.abs(jz_gj_cz));
				} else {
					dataMap.put(prefix + "_jz_gj_cz", "相等");
				}
			}
			if (i == xqTableList.size() - 1) {
				if(prefix.contains("fajh")||prefix.contains("gzl")||prefix.contains("gjlj")||prefix.contains("csx")||prefix.contains("shzl")) {
					dataMap.put(prefix+"_rank1", zs);
				}else {
					dataMap.put(prefix+"_rankh", zs);
				}
			}
		}
    }
    public void getZyDataIntoMap(Map dataMap, List zjTableList, String fybm,String prefix) {//中院报告第一段
    	double sumyear = 0;
    	double sumlastyear = 0;
    	double sumhb = 0;
    	double zs = 0;
		double zsLastYear = 0;
		double cz = 0;
		double zyzs = 0;
		String fy = "";
		dataMap.put(prefix+"_rank1", "--");
		dataMap.put(prefix+"_rankh", "--");
		List zjTableList3 = new ArrayList();
		Map rowSelf = new HashMap();
		/*StringBuffer rankXQ3 = new StringBuffer();//全市前三
		StringBuffer rankXQH3 = new StringBuffer();// 全市后三
		if(zjTableList.size()>3) {
			for(int i=1;i<4;i++) {
				Map row = (Map) zjTableList.get(zjTableList.size()-i);
				rankXQH3.append(row.get("fy").toString() + "（" + NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2) + "）");
				rankXQH3.append("、");
			}
			for(int i=0;i<3;i++) {
				Map row = (Map) zjTableList.get(i);
				rankXQ3.append(row.get("fy").toString() + "（" + NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2) + "）");
				rankXQ3.append("、");
			}
		}
		dataMap.put(prefix + "_rank3_xq", rankXQ3.length()>1?rankXQ3.substring(0, rankXQ3.length()-1):"--");//全市排名前三
		dataMap.put(prefix + "_rankh3_xq", rankXQH3.length()>1?rankXQH3.substring(0, rankXQH3.length()-1):"--");//全市排名后三
*/    	Iterator it = zjTableList.iterator();
		while(it.hasNext()) {
			Map row = (Map)it.next();
			zs = NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2);
			zsLastYear = NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2);
			cz = NumUtil.toNxiaoshu(row.get("cz").toString(), 2);
			if(zs==999999) {
				it.remove();
			}
		}
		for(int i=0;i<zjTableList.size();i++) {
			Map row = (Map)zjTableList.get(i);
			zs = NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2);
			zsLastYear = NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2);
			cz = NumUtil.toNxiaoshu(row.get("cz").toString(), 2);
			fy = row.get("fy").toString();
			row.put("lastyearzs", zsLastYear);
			row.put("yearzs", zs);
			row.put("cz", cz);
			double hbzs = row.get("hbzs")!=null?NumUtil.toNxiaoshu(row.get("hbzs").toString(), 2):0;
			double hbcz = row.get("hbcz")!=null?NumUtil.toNxiaoshu(row.get("hbcz").toString(), 2):0;
			row.put("hbzs", hbzs);
			row.put("hbcz", hbcz);
			if(prefix.contains("gzl")) {
				sumyear = sumyear + Math.abs(zs);
				sumlastyear = sumlastyear + Math.abs(zsLastYear);
				sumhb = sumhb + Math.abs(hbzs);
			}else {
				sumyear = sumyear + zs;
				sumlastyear = sumlastyear + zsLastYear;
				sumhb = sumhb + hbzs;
			}
			if(i == 0) {
				dataMap.put(prefix+"_rank1", zs);
				row.put("id", "1");
				if(prefix.contains("fajh")||prefix.contains("gzl")||prefix.contains("gjlj")||prefix.contains("csx")||prefix.contains("shzl")) {
					if(fybm.startsWith("1")||fybm.startsWith("2")||fybm.startsWith("M")||fybm.startsWith("9")) {
						row.put("fy", "本市最小值");
	            	}else {
	            		row.put("fy", "省内最小值");
	            	}	
				}else {
					if(fybm.startsWith("1")||fybm.startsWith("2")||fybm.startsWith("M")||fybm.startsWith("9")) {
						row.put("fy", "本市最大值");
	            	}else {
	            		row.put("fy", "省内最大值");
	            	}	
				}
				zjTableList3.add(row);
			}
			if(row.get("jbfy").toString().equals(fybm)) {
				zyzs = zs;
				if(prefix.equals("sfgl_csx")) {
					dataMap.put("sfgl_csxzs", zs);
	        		if(cz>0) {
	        			dataMap.put("sfgl_csx_tb", "上升"+Math.abs(cz));//同比
	        		}else if(cz<0) {
	        			dataMap.put("sfgl_csx_tb", "下降"+Math.abs(cz));//同比
	        		}else {
	        			dataMap.put("sfgl_csx_tb", "不变");//同比
	        		}
				}
				dataMap.put(prefix + "_rank_zj", "第"+NumUtil.toChineseNum(i+1)+"位");
				rowSelf.put("id", "2");
            	rowSelf.put("lastyearzs", zsLastYear);
            	rowSelf.put("yearzs", zs);
            	rowSelf.put("cz", cz);
            	rowSelf.put("fy", fy);
            	rowSelf.put("hbzs", hbzs);
            	rowSelf.put("hbcz", hbcz);
            	zjTableList3.add(rowSelf);
            }/*else {
            	if(fybm.startsWith("1")||fybm.startsWith("2")||fybm.startsWith("M")||fybm.startsWith("9")) {
            		row.put("fy", "**中院");
            	}else {
            		row.put("fy", "**法院");
            	}	
            }*/
			if (i == zjTableList.size() - 1) {
				dataMap.put(prefix+"_rankh", zs);
				row.put("id", "3");
				if(prefix.contains("fajh")||prefix.contains("gzl")||prefix.contains("gjlj")||prefix.contains("csx")||prefix.contains("shzl")) {
					if(fybm.startsWith("1")||fybm.startsWith("2")||fybm.startsWith("M")||fybm.startsWith("9")) {
						row.put("fy", "本市最大值");
	            	}else {
	            		row.put("fy", "省内最大值");
	            	}	
				}else {
					if(fybm.startsWith("1")||fybm.startsWith("2")||fybm.startsWith("M")||fybm.startsWith("9")) {
						row.put("fy", "本市最小值");
	            	}else {
	            		row.put("fy", "省内最小值");
	            	}
				}
				zjTableList3.add(row);
			}
		}
		double jzyear = NumUtil.toNxiaoshu(zjTableList.size()==0?0:sumyear / zjTableList.size(), 2);// 中院所属高院辖区均值
		double jzlastyear = NumUtil.toNxiaoshu(zjTableList.size()==0?0:sumlastyear / zjTableList.size(), 2);// 中院所属高院辖区均值(去年)
		double jzhb = NumUtil.toNxiaoshu(zjTableList.size()==0?0:sumhb / zjTableList.size(), 2);// 中院所属高院辖区均值(环比)
		dataMap.put(prefix + "_jz_zj", jzyear);
		double jzcz = NumUtil.toNxiaoshu(zyzs - jzyear, 2);
		if (jzcz > 0) {// 和均值做比较
			dataMap.put(prefix + "_jz_zj_cz", "高" + Math.abs(jzcz));
			dataMap.put(prefix + "_fxjb", "高");//风险级别
		} else if (jzcz < 0) {
			dataMap.put(prefix + "_jz_zj_cz", "低" + Math.abs(jzcz));
			dataMap.put(prefix + "_fxjb", "低");//风险级别
		} else {
			dataMap.put(prefix + "_jz_zj_cz", "相等");
			dataMap.put(prefix + "_fxjb", "高");//风险级别
		}
		Map row = new HashMap();
		row.put("id", "4");
		if (fybm.startsWith("1") || fybm.startsWith("2") || fybm.startsWith("M") || fybm.startsWith("9")) {
			row.put("fy", "本市平均值");
		} else {
			row.put("fy", "省内平均值");
		}
		row.put("lastyearzs", jzlastyear);
		row.put("yearzs", jzyear);
		row.put("cz", NumUtil.toNxiaoshu(jzyear - jzlastyear, 2));
		row.put("hbzs", jzhb);
		row.put("hbcz", NumUtil.toNxiaoshu(jzyear - jzhb, 2));
		zjTableList3.add(row);
		zjTableList.clear();
		zjTableList.addAll(zjTableList3);
    	
    }
    
    public void getQxDetailDataIntoMap(Map dataMap, List xqTableList,List zylist, String fymc,String prefix) {//中院报告中第三段基层法院描述
    	/*StringBuffer rankQX3 = new StringBuffer();//区县前三
    	List listQX3 = new ArrayList();*/
		String rankQX1="";//区县第一
		for(int i=0;i<xqTableList.size();i++) {
			Map row = (Map)xqTableList.get(i);
			row.put("id", i+2);
			row.put("lastyearzs", NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2));
			row.put("yearzs", NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2));
			row.put("cz", NumUtil.toNxiaoshu(row.get("cz").toString(), 2));
			row.put("fy",row.get("fy").toString().replaceAll("(北京|上海|天津|重庆)(市)?", ""));
			double hbzs = row.get("hbzs")!=null?NumUtil.toNxiaoshu(row.get("hbzs").toString(), 2):0;
			double hbcz = row.get("hbcz")!=null?NumUtil.toNxiaoshu(row.get("hbcz").toString(), 2):0;
			row.put("hbzs", hbzs);
			row.put("hbcz", hbcz);
			if(i==0) {
				rankQX1 = row.get("fy").toString();
			}
		}
		dataMap.put(prefix+"_rank1_qx", rankQX1);		
		StringBuffer rankXQ3 = new StringBuffer();//全市前三
		StringBuffer rankXQH3 = new StringBuffer();// 全市后三
		if(xqTableList.size()>3) {
			for(int i=1;i<4;i++) {
				Map row = (Map) xqTableList.get(xqTableList.size()-i);
				rankXQH3.append(row.get("fy").toString() + "（" + NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2) + "）");
				rankXQH3.append("、");
			}
			for(int i=0;i<3;i++) {
				Map row = (Map) xqTableList.get(i);
				rankXQ3.append(row.get("fy").toString() + "（" + NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2) + "）");
				rankXQ3.append("、");
			}
		}
		dataMap.put(prefix+"_rank3_qx", rankXQ3.length()>1?rankXQ3.substring(0, rankXQ3.length()-1):"--");//全市排名前三
		dataMap.put(prefix + "_rankh3_qx", rankXQH3.length()>1?rankXQH3.substring(0, rankXQH3.length()-1):"--");//全市排名后三
		if(zylist.size()>0) {
			Map zyself = (Map)zylist.get(0);
			zyself.put("id", 1);
			zyself.put("fy", fymc.replace("法院", "中院"));
			zyself.put("lastyearzs", NumUtil.toNxiaoshu(zyself.get("lastyearzs").toString(), 2));
			zyself.put("yearzs", NumUtil.toNxiaoshu(zyself.get("yearzs").toString(), 2));
			zyself.put("cz", NumUtil.toNxiaoshu(zyself.get("cz").toString(), 2));
			zyself.put("hbzs", zyself.get("hbzs")!=null?NumUtil.toNxiaoshu(zyself.get("hbzs").toString(), 2):"--");
			zyself.put("hbcz", zyself.get("hbcz")!=null?NumUtil.toNxiaoshu(zyself.get("hbcz").toString(), 2):"--");
			xqTableList.add(0,zyself);
		}
    }
    //直辖市中院排名表格
    public List getZjZxTableList(String tableName, String type, String date, String fybm, boolean desc,String casetype){ 
    	StringBuffer sql = new StringBuffer();
    	if(type.equals("year")) {
    		sql = new StringBuffer( "SELECT\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc AS fy,\r\n" + 
        			"	sum( CASE WHEN date = '"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' THEN result ELSE 0 END ) AS lastyearzs,\r\n" + 
        			"	sum( CASE WHEN date = '"+date+"' THEN result ELSE 0 END ) AS yearzs,\r\n" + 
        			"	(\r\n" + 
        			"	sum( CASE WHEN date = '"+date+"' THEN result ELSE 0 END ) - sum( CASE WHEN date = '"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' THEN result ELSE 0 END ) \r\n" + 
        			"	) AS cz \r\n" + 
        			"FROM\r\n" + 
        			"	"+tableName+",\r\n" + 
        			"	sfzs_czrk \r\n" + 
        			"WHERE\r\n" + 
        			"	"+tableName+".jbfy = sfzs_czrk.fybm \r\n");
    		    	if(!tableName.equals("sfzs_result_csx")) {
    					sql.append(" and type='"+type+"'");
    				} 
        			if(!casetype.equals("")) {
        	    		sql.append(" and casetype='"+casetype +"'");
        	    	}
        			sql.append("	AND sfzs_czrk.YEAR = '"+NumUtil.getPastDate(date,"yyyy")+"' \r\n" + 
        			"	AND kind = '辖' \r\n" + 
        			"	AND jbfy LIKE '"+fybm.substring(0, 1)+"%' AND right(jbfy,1)='0' AND jbfy <>'"+fybm.substring(0, 1)+"00'\r\n" + 
        			"GROUP BY\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc \r\n" + 
        			"ORDER BY\r\n" + 
        			"	yearzs");
        	if(desc) {
        		sql.append(" desc");
        	}
    	}else {
    		sql = new StringBuffer( "select jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-hbzs) as hbcz,(yearzs-lastyearzs) as cz from(SELECT\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc AS fy,\r\n" + 
        			"	sum( CASE WHEN date = '"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' and type='"+type+"' THEN result ELSE 0 END ) AS lastyearzs,\r\n" + 
        			"	sum( CASE WHEN date = '"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' THEN result ELSE 0 END ) AS hbzs,\r\n" + 
        			"	sum( CASE WHEN date = '"+date+"' and type='"+type+"' THEN result ELSE 0 END ) AS yearzs\r\n" + 
        			"FROM\r\n" + 
        			"	"+tableName+",\r\n" + 
        			"	sfzs_czrk \r\n" + 
        			"WHERE\r\n" + 
        			"	"+tableName+".jbfy = sfzs_czrk.fybm \r\n");
        			if(!casetype.equals("")) {
        	    		sql.append(" and casetype='"+casetype +"'");
        	    	}
        			sql.append("	AND sfzs_czrk.YEAR = '"+NumUtil.getPastDate(date,"yyyy")+"' \r\n" + 
        			"	AND kind = '辖' \r\n" + 
        			"	AND jbfy LIKE '"+fybm.substring(0, 1)+"%' AND right(jbfy,1)='0' AND jbfy <>'"+fybm.substring(0, 1)+"00'\r\n" + 
        			"GROUP BY\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc \r\n" + 
        			"ORDER BY\r\n" + 
        			"	yearzs");
        	if(desc) {
        		sql.append(" desc");
        	}
        	sql.append(")t");
    	}
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    public List getTdZxTableList(String tableName, String type, String date, String fybm, boolean desc,String casetype){
    	StringBuffer sql = new StringBuffer();
    	if(type.equals("year")) {
    		sql = new StringBuffer("SELECT jbfy,fyjc as fy,\r\n" + 
        			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )as lastyearzs,\r\n" + 
        			"sum(case when date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
        			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )) as cz from "+tableName+",sfzs_czrk where "+tableName+".jbfy=sfzs_czrk.fybm");
        	if(!tableName.equals("sfzs_result_csx")) {
    			sql.append(" and type='"+type+"'");
    		} 
        	if(!casetype.equals("")) {
        		sql.append(" and casetype='"+casetype +"'");
        	}
        	sql.append(" and sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and kind ='辖' AND (jbfy like'1%0' or jbfy like'2%0' or jbfy like'9%0' or jbfy like'M%0' )\r\n" + 
        			"	AND jbfy not in('100','200','900','M00') GROUP BY jbfy,fyjc ORDER BY yearzs");
        	if(desc) {
        		sql.append(" desc");
        	}
    	}else {
    		sql = new StringBuffer("select jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-hbzs) as hbcz,(yearzs-lastyearzs) as cz from(SELECT jbfy,fyjc as fy,\r\n" + 
        			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' and type='"+type+"' then result else 0 end )as lastyearzs,\r\n" + 
        			"sum(case when date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then result else 0 end) as hbzs,\r\n" + 
        			"sum(case when date='"+date+"' and type='"+type+"' then result else 0 end) as yearzs from "+tableName+",sfzs_czrk where "+tableName+".jbfy=sfzs_czrk.fybm");
        	if(!casetype.equals("")) {
        		sql.append(" and casetype='"+casetype +"'");
        	}
        	sql.append(" and sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and kind ='辖' AND (jbfy like'1%0' or jbfy like'2%0' or jbfy like'9%0' or jbfy like'M%0' )\r\n" + 
        			"	AND jbfy not in('100','200','900','M00') GROUP BY jbfy,fyjc ORDER BY yearzs");
        	if(desc) {
        		sql.append(" desc");
        	}
        	sql.append(")t");
    	}
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    
    //庭室排名表格
    public List getTsTableList(String table, String type, String date, String fybm,boolean dese,String casetype){  
    	StringBuffer sql = new StringBuffer();
    	if(type.equals("year")) {
    		sql = new StringBuffer("SELECT cbspt as fy,\r\n" + 
        			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )as lastyearzs,\r\n" + 
        			"sum(case when date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
        			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )) as cz from "+table+"_cbspt where kind ='辖'");
        	if(!table.equals("sfzs_result_csx")) {
    			sql.append(" and type='"+type+"'");
    		} 
        	if(!casetype.equals("")) {
        		sql.append(" and casetype='"+casetype +"'");
        	}
        	sql.append(" and jbfy='"+fybm+"' GROUP BY cbspt ORDER BY yearzs");
        	if(dese) {
        		sql.append(" desc");
        	}   	
    	}else {
    		sql = new StringBuffer("select fy,lastyearzs,hbzs,yearzs,(yearzs-hbzs) as hbcz,(yearzs-lastyearzs) as cz from(SELECT cbspt as fy,\r\n" + 
        			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' and type='"+type+"' then result else 0 end )as lastyearzs,\r\n" + 
        			"sum(case when date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then result else 0 end) as hbzs,\r\n" + 
        			"sum(case when date='"+date+"' and type='"+type+"' then result else 0 end) as yearzs from "+table+"_cbspt where kind ='辖'");
        	if(!casetype.equals("")) {
        		sql.append(" and casetype='"+casetype +"'");
        	}
        	sql.append(" and jbfy='"+fybm+"' GROUP BY cbspt ORDER BY yearzs");
        	if(dese) {
        		sql.append(" desc");
        	} 
        	sql.append(")t");
    	}
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    
    public void getTsDataIntoMap(Map dataMap, List tsTableList, String fybm,String prefix) {//基层报告
    	double zs = 0;
		double zsLastYear = 0;
		double cz = 0;
    	String cbspt = "";
    	Map rowSelf = new HashMap();
    	dataMap.put(prefix+"_ts_max", "--");
		dataMap.put(prefix+"_ts_rank1", "--");
    	dataMap.put(prefix+"_ts_min", "--");
		dataMap.put(prefix+"_ts_rankh", "--");
		Iterator it = tsTableList.iterator();
		while(it.hasNext()) {
			Map row = (Map)it.next();
    		zs = NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2);
			zsLastYear = NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2);
			cz = NumUtil.toNxiaoshu(row.get("cz").toString(), 2);
			cbspt = row.get("fy")==null?"--":row.get("fy").toString();
			row.put("lastyearzs", zsLastYear==999999?"--":zsLastYear);
			row.put("yearzs", zs==999999?"--":zs);
			row.put("cz", zsLastYear==999999||zs==999999?"--":cz);
			row.put("fy", cbspt);
			double hbzs = row.get("hbzs")!=null?NumUtil.toNxiaoshu(row.get("hbzs").toString(), 2):0;
			double hbcz = row.get("hbcz")!=null?NumUtil.toNxiaoshu(row.get("hbcz").toString(), 2):0;
			row.put("hbzs", hbzs==999999?"--":hbzs);
			row.put("hbcz", hbzs==999999||zs==999999?"--":hbcz);
			if(zs==0 && zsLastYear==0) {
				it.remove();
			}
		}
		/*for(int i=0;i<tsTableList.size();i++) {
    		Map row = (Map)tsTableList.get(i);
    		zs = NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2);
			zsLastYear = NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2);
			cz = NumUtil.toNxiaoshu(row.get("cz").toString(), 2);
			cbspt = row.get("fy")==null?"--":row.get("fy").toString();
			row.put("lastyearzs", zsLastYear);
			row.put("yearzs", zs);
			row.put("cz", cz);
			row.put("lastyearzs", zsLastYear==999999?"--":zsLastYear);
			row.put("yearzs", zs==999999?"--":zs);
			row.put("cz", zsLastYear==999999||zs==999999?"--":cz);
			row.put("fy", cbspt);
			if(zs==0 && zsLastYear==0) {
				tsTableList.remove(i);
			}
			
    	}*/
    	for(int i=0;i<tsTableList.size();i++) {
			Map row = (Map)tsTableList.get(i);
			row.put("id", i+1);
			if(i == 0) {
				if(prefix.contains("fajh")||prefix.contains("gzl")||prefix.contains("gjlj")||prefix.contains("csx")||prefix.contains("shzl")) {
					dataMap.put(prefix+"_ts_min", row.get("yearzs").toString());
					dataMap.put(prefix+"_ts_rankh", row.get("fy").toString());
				}else {
					dataMap.put(prefix+"_ts_max", row.get("yearzs").toString());
					dataMap.put(prefix+"_ts_rank1", row.get("fy").toString());
				}
			}
			//if (i == tsTableList.size() - 1) {
			if (!row.get("yearzs").toString().equals("--")) {
				if(prefix.contains("fajh")||prefix.contains("gzl")||prefix.contains("gjlj")||prefix.contains("csx")||prefix.contains("shzl")) {
					dataMap.put(prefix+"_ts_max", row.get("yearzs").toString());
					dataMap.put(prefix+"_ts_rank1", row.get("fy").toString());
				}else {
					dataMap.put(prefix+"_ts_min", row.get("yearzs").toString());
					dataMap.put(prefix+"_ts_rankh", row.get("fy").toString());
				}
			}
		}
    	
    }
    //全国排名表格
	public List getQgTableList(String table, String type, String date, boolean isQuanguo, boolean desc,String casetype) {
		StringBuffer sql = new StringBuffer();
		if(type.equals("year")) {
			sql = new StringBuffer("select  jbfy,fy,lastyearzs,yearzs,cz,rank() over(order by yearzs");
			if(desc) {
				sql.append(" desc");
			}
			sql.append(") as rank from(SELECT jbfy,fymc as fy,\r\n"
							+ "sum(case when date='" + NumUtil.getPastDate(date,"yyyy-MM-dd")
							+ "' then result else 0 end )as lastyearzs,\r\n" + "sum(case when date='" + date
							+ "' then result else 0 end) as yearzs,\r\n" + "(sum(case when date='" + date
							+ "' then result else 0 end) - sum(case when date='" + NumUtil.getPastDate(date,"yyyy-MM-dd")
							+ "' then result else 0 end )) as cz from "+table+" where (jbfy not in('X00','000') or jbfy is null)");
			if (isQuanguo) {
				sql.append(" and fymc='全国'");
			} else {
				sql.append(" and fymc!='全国'");
			}
			if(!casetype.equals("")) {
				sql.append(" and casetype='"+casetype+"'");
			}
			if(!table.equals("sfzs_result_csx")) {
    			sql.append(" and type='"+type+"'");
    		}
			sql.append(" and date in('" + date + "','" + NumUtil.getPastDate(date,"yyyy-MM-dd")
					+ "') and kind ='高' GROUP BY jbfy,fymc)t");
		}else {
			sql = new StringBuffer("SELECT\r\n" + 
					"	jbfy,\r\n" + 
					"	fy,\r\n" + 
					"	lastyearzs,\r\n" + 
					"	yearzs,\r\n" + 
					"	cz,\r\n" + 
					"	hbzs,\r\n" + 
					"	hbcz,\r\n" + 
					"	RANK () OVER ( ORDER BY yearzs DESC ) AS RANK \r\n" + 
					"FROM\r\n" + 
					"	(\r\n" + 
					"SELECT\r\n" + 
					"	jbfy,\r\n" + 
					"	fymc AS fy,\r\n" + 
					"	SUM ( CASE WHEN DATE = '"+ (Integer.parseInt(date) - 1)+"' AND TYPE = '"+type+"'  THEN RESULT ELSE 0 END ) AS lastyearzs,\r\n" + 
					"	SUM ( CASE WHEN DATE = '"+NumUtil.getHalfYearReportDate(date, type)+"' AND TYPE = '"+NumUtil.getHalfYearReportType(type)+"'  THEN RESULT ELSE 0 END ) AS hbzs,\r\n" + 
					"	SUM ( CASE WHEN DATE = '"+date+"' AND TYPE = '"+type+"' THEN RESULT ELSE 0 END ) AS yearzs,\r\n" + 
					"	(\r\n" + 
					" SUM ( CASE WHEN DATE = '"+date+"' AND TYPE = '"+type+"' THEN RESULT ELSE 0 END ) - SUM ( CASE WHEN DATE = '"+ (Integer.parseInt(date) - 1)+"' AND TYPE = '"+type+"'  THEN RESULT ELSE 0 END )) AS cz ,\r\n" + 
					"	(\r\n" + 
					"	SUM ( CASE WHEN DATE = '"+date+"' AND TYPE = '"+type+"' THEN RESULT ELSE 0 END ) - SUM ( CASE WHEN DATE = '"+NumUtil.getHalfYearReportDate(date, type)+"' AND TYPE = '"+NumUtil.getHalfYearReportType(type)+"'  THEN RESULT ELSE 0 END 	)) AS hbcz \r\n" + 
					"FROM\r\n" + 
					table +
					" WHERE \r\n" + 
					"	( jbfy NOT IN ( 'X00', '000' ) OR jbfy IS NULL ) \r\n");
					if (isQuanguo) {
						sql.append(" and fymc='全国'");
					} else {
						sql.append(" and fymc!='全国'");
					} 
					if(!casetype.equals("")) {
						sql.append(" and casetype='"+casetype+"'");
					}
					sql.append("	AND DATE IN ( '"+date+"', '"+ (Integer.parseInt(date) - 1)+"' ) \r\n" + 
					"	AND kind = '高' \r\n" + 
					"GROUP BY\r\n" + 
					"	jbfy,\r\n" + 
					"	fymc \r\n" + 
					"	)T");
		}
		Query query = entityManager.createNativeQuery(sql.toString());
		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List rows = query.getResultList();
		return rows;
	}
	//中院排名表格
    public List getZjTableList(String table, String type, String date, String fybm, boolean desc,String casetype){  
    	StringBuffer sql = new StringBuffer();
    	if(type.equals("year")) {
    		sql.append("SELECT jbfy,fyjc as fy,\r\n" + 
        			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )as lastyearzs,\r\n" + 
        			"sum(case when date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
        			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )) as cz from "+table+",sfzs_czrk where "+table+".jbfy=sfzs_czrk.fybm");
    		if(!table.equals("sfzs_result_csx")) {
    			sql.append(" and type='"+type+"'");
    		}
    		sql.append(" and sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"'and kind ='中' and jbfy like'"+fybm.substring(0, 1)+"%' ");
    		if(!casetype.equals("")) {
    			sql.append(" and casetype='"+casetype+"'");
			}
    		sql.append(" GROUP BY jbfy,fyjc ORDER BY yearzs");
    		if(desc) {
    			sql.append(" desc");
	        }		
    	}else {
    		sql.append("SELECT jbfy,fyjc as fy,\r\n" + 
    				"	SUM ( CASE WHEN DATE = '"+ (Integer.parseInt(date) - 1)+"' AND TYPE = '"+type+"'  THEN RESULT ELSE 0 END ) AS lastyearzs,\r\n" + 
					"	SUM ( CASE WHEN DATE = '"+NumUtil.getHalfYearReportDate(date, type)+"' AND TYPE = '"+NumUtil.getHalfYearReportType(type)+"'  THEN RESULT ELSE 0 END ) AS hbzs,\r\n" + 
					"	SUM ( CASE WHEN DATE = '"+date+"' AND TYPE = '"+type+"' THEN RESULT ELSE 0 END ) AS yearzs,\r\n" + 
					"	(\r\n" + 
					" SUM ( CASE WHEN DATE = '"+date+"' AND TYPE = '"+type+"' THEN RESULT ELSE 0 END ) - SUM ( CASE WHEN DATE = '"+ (Integer.parseInt(date) - 1)+"' AND TYPE = '"+type+"'  THEN RESULT ELSE 0 END )) AS cz ,\r\n" + 
					"	(\r\n" + 
					"	SUM ( CASE WHEN DATE = '"+date+"' AND TYPE = '"+type+"' THEN RESULT ELSE 0 END ) - SUM ( CASE WHEN DATE = '"+NumUtil.getHalfYearReportDate(date, type)+"' AND TYPE = '"+NumUtil.getHalfYearReportType(type)+"'  THEN RESULT ELSE 0 END 	)) AS hbcz \r\n" +
        			" from "+table+",sfzs_czrk where "+table+".jbfy=sfzs_czrk.fybm");
    		sql.append(" and sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"'and kind ='中' and jbfy like'"+fybm.substring(0, 1)+"%' ");
    		if(!casetype.equals("")) {
    			sql.append(" and casetype='"+casetype+"'");
			}
    		sql.append(" GROUP BY jbfy,fyjc ORDER BY yearzs");
    		if(desc) {
    			sql.append(" desc");
	        }		
    	} 
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //辖区排名表格
    public List getXqTableList(String table, String type, String date, String fybm, boolean desc, String casetype){  
    	StringBuffer sql = new StringBuffer();
    	if(type.equals("year")) {
    		sql.append("SELECT jbfy,replace(fymc,'人民法院','法院') as fy,\r\n" + 
        			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )as lastyearzs,\r\n" + 
        			"sum(case when date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
        			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )) as cz from "+ table +" where kind ='辖'");
	        if(!table.equals("sfzs_result_csx")) {
	            sql.append(" and type='"+type+"'");
	        }
	        sql.append(" and jbfy like'"+fybm.substring(0, 2)+"%' and jbfy<>'"+fybm.substring(0, 2)+"'||'0' and right(fymc,5)<>'（已撤销）' ");
	    	if(!casetype.equals("")) {
	    		sql.append(" and casetype='"+casetype+"'");
			}
	    	sql.append("GROUP BY jbfy,fymc ORDER BY yearzs");
	    	if(desc) {
	    		sql.append(" desc");
	        }	
    	}else {
    		sql.append("select jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-hbzs) as hbcz,(yearzs-lastyearzs) as cz from(SELECT jbfy,replace(fymc,'人民法院','法院') as fy,\r\n" + 
        			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' and type='"+type+"' then result else 0 end )as lastyearzs,\r\n" + 
        			"sum(case when date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then result else 0 end) as hbzs,\r\n" + 
        			"sum(case when date='"+date+"' and type='"+type+"' then result else 0 end) as yearzs from "+ table +" where kind ='辖'");
	
	        sql.append(" and jbfy like'"+fybm.substring(0, 2)+"%' and jbfy<>'"+fybm.substring(0, 2)+"'||'0' and right(fymc,5)<>'（已撤销）' ");
	    	if(!casetype.equals("")) {
	    		sql.append(" and casetype='"+casetype+"'");
			}
	    	sql.append("GROUP BY jbfy,fymc ORDER BY yearzs");
	    	if(desc) {
	    		sql.append(" desc");
	        }	
	    	sql.append(")t");
    	}
    	
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //高、中院本身指数
    public List getSelfZs(String table, String type, String date, String jbfy, String casetype) {
    	StringBuffer sql = new StringBuffer();
    	if(type.equals("year")) {
    		sql = new StringBuffer("SELECT jbfy,fymc as fy,\r\n"
							+ "sum(case when date='" + NumUtil.getPastDate(date,"yyyy-MM-dd")
							+ "' then result else 0 end )as lastyearzs,\r\n" + "sum(case when date='" + date
							+ "' then result else 0 end) as yearzs,\r\n" + "(sum(case when date='" + date
							+ "' then result else 0 end) - sum(case when date='" + NumUtil.getPastDate(date,"yyyy-MM-dd")
							+ "' then result else 0 end )) as cz from "+table+" where jbfy='"+jbfy+"' ");
    		if(!table.equals("sfzs_result_csx")) {
    			sql.append("and type='"+type+"'");
    		}
    		sql.append(" and date in('" + date + "','" + NumUtil.getPastDate(date,"yyyy-MM-dd")
					+ "') and kind ='辖' ");
    		if(!casetype.equals("")) {
    			sql.append(" and casetype='"+casetype+"'");
    		}
    		sql.append(" GROUP BY jbfy,fymc");
    	}else {
    		sql = new StringBuffer("SELECT jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(yearzs-hbzs) as hbcz from(SELECT jbfy,fymc as fy,\r\n"
					+ "sum(case when date='" + NumUtil.getPastDate(date,"yyyy-MM-dd") + "' and type='"+type+"' then result else 0 end )as lastyearzs,\r\n" 
					+ "sum(case when date='" + NumUtil.getHalfYearReportDate(date, type) + "' and type='"+NumUtil.getHalfYearReportType(type)+"' then result else 0 end) as hbzs,\r\n" 
					+ "sum(case when date='" + date + "' and type='"+type+"' then result else 0 end) as yearzs\r\n" 
					+ "from "+table+" where jbfy='"+jbfy+"' ");
			sql.append(" and date in('" + date + "','" + NumUtil.getPastDate(date,"yyyy-MM-dd")
					+ "') and kind ='辖' ");
			if(!casetype.equals("")) {
				sql.append(" and casetype='"+casetype+"'");
			}
			sql.append(" GROUP BY jbfy,fymc)t");
    	}
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }

    public void handleFb(List fbList,String fymc) {
    	Map zyself = new HashMap();
    	Iterator it = fbList.iterator();
		while(it.hasNext()) {
			Map row = (Map)it.next();
			if(row.get("result")!=null) {
				row.put("lv", NumUtil.toNxiaoshu(row.get("result").toString(), 2)+"%");
			}
			if(row.get("fyjc").toString().contains("中级")) {
				zyself = row;
				zyself.put("id", "1");
				zyself.put("fyjc", fymc.replace("法院", "中院"));
				it.remove();
			}else {
				row.put("fyjc",row.get("fyjc").toString().replaceAll("(北京|上海|天津|重庆)(市)?", ""));
			}
		}
		fbList.add(0,zyself);
		for (int i = 0; i < fbList.size(); i++) {
			Map row = (Map) fbList.get(i);
			row.put("id", i+1);
		}
    }

}
